global tables ".\results\tables"
use ".\data\raw\raw5mindat_I10_post.dta", clear
duplicates drop station date hour min, force
keep if dir=="W"
// I-10W
g ELsegment1=cond(abs_pm>=25.29 & abs_pm<27.8 ,1,0) 
g ELsegment2=cond(abs_pm>=24.32 & abs_pm<25.29,1,0)
g ELsegment3=cond(abs_pm>=21.62 & abs_pm<24.32, 1, 0)
g ELsegment4=cond(abs_pm>=18.16 & abs_pm<21.62,1,0)


*** By route, merge route number to constituent segments using concordance
set more off
sort ELsegment
tempfile data
save `data'
forval ri=1/18{
	use ".\data\clean\route_ELsegmentconcordance_I10W.dta", clear
	keep if rt_id==`ri'
	ren elsegment ELsegment
	sort ELsegment
	merge ELsegment using `data', uniqmaster
	drop if _m!=3
	tempfile rt`ri'
	save rt`ri'
	}

use rt1,clear
forval ri=2/18{
	append using rt`ri'
	}

collapse flow  speed,by(date hour min rt_id type) // Collapse to route level
foreach ln in HV ML{
	foreach v in flow speed{
		g `ln'`v'=cond(type=="`ln'",`v',0)
		}
	}
collapse HV* ML*, by(date hour min rt_id)


	

g month=month(date)
g day=day(date)
g year=year(date)
sort rt_id date hour min
save ".\data\clean\I10W_speedflow_premerge", replace






//I-10 E
use ".\data\clean\raw5mindat_I10_post.dta", clear
duplicates drop station date hour min, force
keep if dir=="E"

g ELsegment1=cond(abs_pm>18.69 & abs_pm<=22.94,1,0)
g ELsegment2=cond(abs_pm>22.94 & abs_pm<=24.31,1,0)
g ELsegment3=cond(abs_pm>24.31 & abs_pm<=27.78,1,0)



*** By route, merge route number to constituent segments using concordance
set more off
sort ELsegment
tempfile data
save `data'
forval ri=1/18{
	use ".\data\clean\route_ELsegmentconcordance_I10E.dta", clear
	keep if rt_id==`ri'
	ren elsegment ELsegment
	sort ELsegment
	merge ELsegment using `data', uniqmaster
	drop if _m!=3
	tempfile rt`ri'
	save rt`ri'
	}

use rt1,clear
forval ri=2/18{
	append using rt`ri'
	}

collapse flow  speed,by(date hour min rt_id type) // Collapse to route level
foreach ln in HV ML{
	foreach v in flow speed{
		g `ln'`v'=cond(type=="`ln'",`v',0)
		}
	}
collapse HV* ML*, by(date hour min rt_id)


	

g month=month(date)
g day=day(date)
g year=year(date)
sort rt_id date hour min
save ".\data\clean\I10W_speedflow_premerge", replace
